package com.atguigu.jinx.mapper;

import com.atguigu.jinx.entity.dto.WithdrawRequest;
import com.atguigu.jinx.entity.vo.UserDisplayBalanceIneStatisticsVO;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * Mapper 接口
 * </p>
 *
 * @author steve
 * @since 2025-08-07
 */
@Mapper
public interface WithdrawRequestMapper extends BaseMapper<WithdrawRequest> {


    @Select("""
            WITH RECURSIVE months(month) AS (
                SELECT #{startMonth} AS month
                UNION ALL
                SELECT DATE_FORMAT(DATE_ADD(CONCAT(month, '-01'), INTERVAL 1 MONTH), '%Y-%m')
                FROM months
                WHERE DATE_ADD(CONCAT(month, '-01'), INTERVAL 1 MONTH) <= #{endDate}
            )
            SELECT
                m.month AS month,
                COALESCE(r.total_amount, 0) AS incomeMoney,
                COALESCE(w.total_amount, 0) AS expenditureMoney
            FROM months m
            LEFT JOIN
                (SELECT DATE_FORMAT(withdraw_datetime, '%Y-%m') AS month,
                        SUM(amount) AS total_amount
                 FROM withdraw_request
                 WHERE user_id = #{userId}
                   AND withdraw_datetime BETWEEN #{startDate} AND #{endDate}
                 GROUP BY DATE_FORMAT(withdraw_datetime, '%Y-%m')) w
            ON m.month = w.month
            LEFT JOIN
                (SELECT DATE_FORMAT(paid_at, '%Y-%m') AS month,
                        SUM(amount) AS total_amount
                 FROM recharge_request
                 WHERE user_id = #{userId}
                   AND paid_at BETWEEN #{startDate} AND #{endDate}
                 GROUP BY DATE_FORMAT(paid_at, '%Y-%m')) r
            ON m.month = r.month
            ORDER BY m.month;
            """)
    List<UserDisplayBalanceIneStatisticsVO.IncomeWithdrawalStatisticsData> selectByMonthGroup(
            @Param("userId") Long userId,
            @Param("startMonth") String startMonth,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);
}
